/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jlibrarian;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.EnumMap;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.DataSource;
import java.util.Map;
import java.util.Properties;
import org.apache.derby.jdbc.ClientDataSource;

/**
 *
 * @author xludvic
 */
public class DbManager {
    
    private final DataSource source;
    private static InputStream is = null;
    private static Properties prop = null;
    private static final String PROPERTY_PATH = "jLibConfig.properties";
    private Map<Tables, String> sql = new EnumMap<Tables, String>(Tables.class);

    /**
     * known tables in Database
     */
    public enum Tables {

        books, customers, rents
    }

    public DbManager(DataSource dataSource) {
        this.source = dataSource;

        sql.put(Tables.books,
                " CREATE TABLE books ("
                + "bid integer NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "title varchar(100) NOT NULL, "
                + "authors varchar(100) NOT NULL, "
                + "note varchar(1000) NOT NULL, "
                + "quantity integer DEFAULT 0 NOT NULL "
                + ")");

        sql.put(Tables.customers,
                "CREATE TABLE customers (cid integer NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + " firstname varchar(100) NOT NULL, "
                + "surname varchar(100) NOT NULL, "
                + "born timestamp NOT NULL, "
                + "city varchar(100) NOT NULL, "
                + "street varchar(100) NOT NULL, "
                + "telephone varchar(100) NOT NULL "
                + ")");

        sql.put(Tables.rents,
                "CREATE TABLE rents ("
                + "rid integer NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),  "
                + "cid integer NOT NULL, "
                + "bid integer NOT NULL, "
                + "borrowed timestamp NOT NULL, "
                + "returned timestamp, "
                + "toreturn timestamp NOT NULL "
                + ")");
    }

    static public DataSource getDataSource() {
        ClientDataSource ds = new ClientDataSource();
        ds.setServerName(getProperty("dbServer"));
        ds.setDatabaseName(getProperty("dbName"));
        ds.setPortNumber(Integer.parseInt(getProperty("dbPort")));
        ds.setUser(getProperty("dbUname"));
        ds.setPassword(getProperty("dbPasswd"));
        return ds;
    }

    public boolean createTable(Tables table) {
        Connection conn = null;
        PreparedStatement st = null;
        boolean success = false;

        try {
            conn = source.getConnection();
            st = conn.prepareStatement(sql.get(table));

            int res = st.executeUpdate();
            if (res == 0) {
                success = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
            throw new JLibException(ex);
        } finally {
            if (st != null) {
                try {
                    st.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(DbManager.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }

        return success;
    }

    public boolean createAllTables() {
        Iterator<Tables> i = sql.keySet().iterator();
        boolean succ = true;
        while (i.hasNext()) {
            succ = this.createTable(i.next());
            if (!succ) {
                return false;
            }
        }
        return true;
    }

    /**
     * This method returns property from specified file
     * @param propName property key
     * @return returns value for given key
     */
    public static String getProperty(String propName) {
        if (is == null) {
            try {
                initProperty();
            }
            catch(IOException e){
                throw new JLibException(e);
            }
        }
        return prop.getProperty(propName);
    }

    /**
     * Private method, creates instance of input stream and properties
     */
    private static void initProperty() throws IOException {
        is = DbManager.class.getResourceAsStream(PROPERTY_PATH);
        prop = new Properties();
        prop.load(is);
    }
}
